﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class SubCategoriesDA
    {
        SqlDataAdapter da;

        public DataSet getSubCategories()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectSubCategory", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
        public DataSet getSubCategoriesByCate(int idCate)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectSubCategoryByCateId", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    da.SelectCommand.Parameters.Add(new SqlParameter("@CategoryID", idCate));
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }
        public bool CreateSubCategories(SubCategory sc)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addSubCategory", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@CategoryId", SqlDbType.Int);
                    command.Parameters["@CategoryId"].Value = sc.Category_ID.ToString();

                    command.Parameters.Add("@SubCategoryName", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryName"].Value = sc.SubCategory_Name.ToString();

                    command.Parameters.Add("@SubCategoryImage", SqlDbType.VarChar);
                    command.Parameters["@SubCategoryImage"].Value = sc.SubCategory_Image.ToString();

                    command.Parameters.Add("@SubCategoryDescription", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryDescription"].Value = sc.SubCategory_Description.ToString();

                    command.Parameters.Add("@SubCategoryArea", SqlDbType.Float);
                    command.Parameters["@SubCategoryArea"].Value = sc.SubCategory_Area.ToString();

                    command.Parameters.Add("@SubCategoryHuong", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryHuong"].Value = sc.SubCategory_Huong.ToString();


                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public SubCategory getSubCategoriesByID(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectSubCategoryById", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@SubCategoryId", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    SubCategory sc = null;
                    if (dr.Read())
                    {
                        int scId = Int32.Parse(dr["SubCategoryID"].ToString());
                        int sId = Int32.Parse(dr["CategoryId"].ToString());
                        String subCateName = dr["SubCategoryName"].ToString();
                        String subCateImage = dr["SubCategoryImage"].ToString();
                        String subCateDes = dr["SubCategoryDescription"].ToString();
                        float subCateArea = float.Parse(dr["SubCategoryArea"].ToString());
                        String subCateHuong = dr["SubCategoryHuong"].ToString();
                        sc = new SubCategory(scId, sId, subCateName, subCateImage, subCateDes, subCateArea, subCateHuong);
                    }
                    connection.Close();
                    return sc;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateSubCategories(SubCategory sc)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateSubCategory", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@SubCategoryID", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryID"].Value = sc.SubCategory_ID.ToString();

                    command.Parameters.Add("@CategoryId", SqlDbType.Int);
                    command.Parameters["@CategoryId"].Value = sc.Category_ID.ToString();

                    command.Parameters.Add("@SubCategoryName", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryName"].Value = sc.SubCategory_Name.ToString();

                    command.Parameters.Add("@SubCategoryImage", SqlDbType.VarChar);
                    command.Parameters["@SubCategoryImage"].Value = sc.SubCategory_Image.ToString();

                    command.Parameters.Add("@SubCategoryDescription", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryDescription"].Value = sc.SubCategory_Description.ToString();

                    command.Parameters.Add("@SubCategoryArea", SqlDbType.Float);
                    command.Parameters["@SubCategoryArea"].Value = sc.SubCategory_Area.ToString();

                    command.Parameters.Add("@SubCategoryHuong", SqlDbType.NVarChar);
                    command.Parameters["@SubCategoryHuong"].Value = sc.SubCategory_Huong.ToString();


                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteSubCategory(SubCategory sc)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeSubCategory", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@SubCategoryId", sc.SubCategory_ID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }


    }
}
